#!/usr/bin/python
'''
wrapper module for sqlite3
'''

import sqlite3
import configparser
import logging

class DbAccess(object):
    """Wrapper class for db_access access"""

    def __init__(self, connectionString):
        config = configparser.ConfigParser()
        config.read("config.ini")
        self._connectionstring = config.get("ConnectionStrings", connectionString)

    def execute(self, sql, *parameters):
        '''
        Execute sql with parameters
        '''
        connection = sqlite3.connect(self._connectionstring)
        try:
            cursor = connection.cursor()
            cursor.execute(sql, DbAccess._detect_tuple_parameter(parameters))
            connection.commit()
            return connection.total_changes
        finally:
            if connection:
                connection.close()

    def executemany(self, sql, paramsarray):
        '''
        Execute sql with an array of paramerter tuples
        '''
        connection = sqlite3.connect(self._connectionstring)
        try:
            cursor = connection.cursor()
            cursor.executemany(sql, paramsarray)
            connection.commit()
            return connection.total_changes
        finally:
            if connection:
                connection.close()

    def executescript(self, sql):
        '''
        Execute sql script with multiply statements, each statement should end with ';'
        '''
        connection = sqlite3.connect(self._connectionstring)
        try:
            cursor = connection.cursor()
            cursor.executescript(sql)
            connection.commit()
            return connection.total_changes
        finally:
            if connection:
                connection.close()

    def fetchone(self, sql, *parameters):
        '''
        Fetch one result by given sql and parameters
        '''
        connection = sqlite3.connect(self._connectionstring)
        try:
            cursor = connection.cursor()
            cursor.execute(sql, DbAccess._detect_tuple_parameter(parameters))
            return cursor.fetchone()
        finally:
            if connection:
                connection.close()

    def fetchmany(self, sql, size=None, *parameters):
        '''
        Fetch many result by given sql and parameters
        '''
        connection = sqlite3.connect(self._connectionstring)
        try:
            cursor = connection.cursor()
            cursor.execute(sql, DbAccess._detect_tuple_parameter(parameters))
            if size:
                return cursor.fetchmany(size)
            return cursor.fetchmany()
        finally:
            if connection:
                connection.close()

    def fetchall(self, sql, *parameters):
        '''
        Fetch all result by using the sql and parameters
        '''
        connection = sqlite3.connect(self._connectionstring)
        try:
            cursor = connection.cursor()
            cursor.execute(sql, DbAccess._detect_tuple_parameter(parameters))
            return cursor.fetchall()
        finally:
            if connection:
                connection.close()

    @staticmethod
    def _detect_tuple_parameter(parameters):
        '''
        Get the real tuple parameters, in some cases user may already pass in a tuple as parameter
        '''
        if parameters and isinstance(parameters, tuple) and isinstance(parameters[0], tuple):
            return parameters[0]
        return parameters

    if __name__ == "__main__":
        from custom_logging import setup_logging
        from db.sqllite3_wrapper import DbAccess
        setup_logging()
        logger = logging.getLogger(__name__)
        db_access = DbAccess("LocalDb")
        allResult = db_access.fetchall("PRAGMA database_list")
        logger.info("%s", allResult)
        ##create
        db_access.execute('''CREATE TABLE IF NOT EXISTS COMPANY
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')
        ##insert
        db_access.executescript("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)\
      VALUES (1, 'Paul', 32, 'California', 20000.00 );\
      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)\
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 );")
        db_access.executemany("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (?,?,?,?,?)",
                              [(3, 'Teddy', 23, 'Norway', 20000.00),
                               (4, 'Mark', 25, 'Rich-Mond ', 65000.00)])

        #replace with named style
        db_access.executemany("REPLACE INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (:id,:name,:age,:address,:salary)",
                              [{"id":3, "name":'Teddy', "age":24, "address":'Norway a', "salary": 20000.00},
                               {"id":1, "name":'Teddy', "age":24, "address":'Norway a', "salary": 20000.00}])
        ##fetch all
        allResult = db_access.fetchall("SELECT id, name, address, salary  from COMPANY;")
        logger.info("%s", allResult)
        ##fetch many
        allResult = db_access.fetchmany("SELECT id,name,address,salary from COMPANY;", 2)
        logger.info("%s", allResult)
        allResult = db_access.fetchmany("SELECT id,name,address,salary from COMPANY WHERE ID > ?;"
                                        , 2, 1)
        logger.info("%s", allResult)
        ##update
        db_access.execute("UPDATE COMPANY set SALARY = ? where ID=?;", 25000.00, 1)
        db_access.execute("UPDATE COMPANY set SALARY = ? where ID=?;", (35000.00, 2))
        ##fetch one
        result = db_access.fetchone("SELECT id, name, address, salary from COMPANY where id = ?;",
                                    1)
        logger.info("%s", result)
        ##delete
        db_access.execute("DELETE from COMPANY where ID=2;")
        ##fetch all
        allResult = db_access.fetchall("SELECT id, name, address, salary  from COMPANY;")
        logger.info("%s", allResult)
        db_access.execute("DROP TABLE COMPANY")
